package cn.com.dhcc.turbo.service.creatTable;

import java.sql.SQLException;
import java.util.Random;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.com.dhcc.app.core.exception.DaoException;
import cn.com.dhcc.app.core.service.BaseService;
import cn.com.dhcc.app.core.util.UUIDGenerator;
import cn.com.dhcc.turbo.dao.creatTable.CreatTableDao;
import cn.com.dhcc.turbo.entity.creatTable.InsertData;
/**
 * 建立测试使用的数据库表
 *
 * @2015年3月6日 @下午3:49:32
 * @author 蒋佩钊
 */
@Service
public class CreatTableService extends BaseService{

	@Autowired
	private CreatTableDao creatTableDao;
	
	private static Random randGen = null;
	private static char[] numbersAndLetters = null;
	
	public void testCreatTableNoKeyTS(String kind, int num) throws DaoException {
		for (int i = 0; i < num; i++) {
			String code = kind + "_" + String.format("%02d", i + 1 );
			creatTableDao.testCreatTableNoKeyTS(code);
		}
	}
	
	public void testCreatTableWithKey(String kind, int num) throws DaoException {
		for (int i = 0; i < num; i++) {
			String code = kind + "_" + String.format("%02d",(i + 1));
			creatTableDao.testCreatTableWithKey(code);
		}
	}
	
	public void testCreatTableWithTS(String kind, int num) throws DaoException {
		for (int i = 0; i < num; i++) {
			String code = kind + "_" + String.format("%02d",(i + 1));
			creatTableDao.testCreatTableWithTS(code);
		}
	}
	
	/**
	 * 将要关联外键列设为唯一键
	 * @param kind 测试的表类别：1/2/3/4/5_A/5_B...
	 * @param num 需要设置外键为唯一键的表个数
	 * @throws DaoException
	 */
	public void testCreatTableWithKeyTS(String kind, int num) throws DaoException {
		for (int i = 0; i < num; i++) {
			String code = kind + "_" + String.format("%02d",(i + 1));
			creatTableDao.testCreatTableWithKeyTS(code);
		}
	}

	/**
	 * 创建外键
	 * @param table 需要建立外建的表类别：1/2/3/4/5_A/5_B...
	 * @param tableCount 需要建立外建的表个数
	 * @param foreign 外键关联的表类别
	 * @param Col 要关联的列
	 * @param b 是否创建唯一键
	 * @throws DaoException
	 */
	public void testCreatTableRelation(String table, int tableCount, String foreign, String Col, boolean b) throws DaoException {
		String foreignCode = foreign + "_" + String.format("%02d", 1 );
		if (b) {
			creatTableDao.testCreatTableUK(foreignCode,Col);
		}
		
		for (int i = 0; i < tableCount; i++) {
			String tableCode = table + "_" + String.format("%02d",(i + 1));
			creatTableDao.testCreatTableFK(tableCode,foreignCode,Col);
		}
	}

	/**
	 * 插入无外键表数据
	 * @param kind 需要建立外建的表类别：1/2/3/4/5_A/5_B...
	 * @param num 此类型表个数
	 * @param count 插入记录的总数
	 * @throws DaoException
	 */
	public void insertDataNoFK(String kind, int num, int count) throws DaoException {
		int kindInt = Integer.parseInt(kind);
		String tablePrefix =  new String();
		switch (kindInt) {
		case 1:
			tablePrefix = "TEST_WITHOUT_KEY_TS_";
			break;
		case 2:
			tablePrefix = "TEST_WITH_KEY_";
			break;
		case 3:
			tablePrefix = "TEST_WITH_TS_";
			break;
		case 4:
			tablePrefix = "TEST_WITH_KEY_TS_";
			break;

		default:
			break;
		}
		for (int i = 0; i < num; i++) {
			String code = kind + "_" + String.format("%02d", i + 1 );
			String tableName = tablePrefix + code;
			for (int j = 0; j < count; j++) {
				InsertData tmpData = new InsertData();
				tmpData.setCol_1(randomString(1));
				tmpData.setCol_2(UUIDGenerator.getUUID());
				tmpData.setCol_3(UUIDGenerator.getUUID());
				tmpData.setCol_4(randomFloat(1,9999));
				tmpData.setCol_5(randomInt(0,9));
				tmpData.setCol_6((float)(randomInt(0,99999999)/100));
				tmpData.setCol_7(UUIDGenerator.getUUID());
				tmpData.setCol_8(UUIDGenerator.getUUID());
				tmpData.setCol_9(UUIDGenerator.getUUID());
				//col_10 sysdate
				//col_11 sysdate
				//col_12 systimestamp //cast(systimestamp as timestamp(6))
				//col_13 systimestamp //cast(systimestamp as timestamp(6))
				creatTableDao.insertDataNoFK(tableName, tmpData);
			}
		}
	}
	
	/**
	 * 
	 * @param kind 表类别：1/2/3/4/5_A/5_B...
	 * @param num 此类型表个数
	 * @param count 插入记录的总数
	 * @param fk
	 * @throws DaoException
	 */
	public void insertDataWithFK(String kind, int num, int count, String[] fk) throws DaoException {
		String tablePrefix =  "TEST_WITH_KEY_TS_";
		for (int i = 0; i < num; i++) {
			String code = kind + "_" + String.format("%02d", i + 1 );
			String tableName = tablePrefix + code;
			for (int j = 0; j < count; j++) {
				InsertData tmpData = new InsertData();
				tmpData.setCol_1(randomString(1));
				tmpData.setCol_2(UUIDGenerator.getUUID());
				tmpData.setCol_3(UUIDGenerator.getUUID());
				tmpData.setCol_4(randomFloat(1,9999));
				tmpData.setCol_5(randomInt(0,9));
				tmpData.setCol_6((float)(randomInt(0,99999999)/100));
				tmpData.setCol_7(UUIDGenerator.getUUID());
				tmpData.setCol_8(UUIDGenerator.getUUID());
				tmpData.setCol_9(UUIDGenerator.getUUID());
				//col_10 sysdate
				//col_11 sysdate
				//col_12 systimestamp //cast(systimestamp as timestamp(6))
				//col_13 systimestamp //cast(systimestamp as timestamp(6))
				for (int k = 0; k < fk.length; k++) {
					String FKKind = fk[k];
					String FKcode = FKKind + "_" + String.format("%02d", 1 );
					String FKtableName = tablePrefix + FKcode;
					creatTableDao.insertDataNoFK(FKtableName, tmpData);
				}
				creatTableDao.insertDataNoFK(tableName, tmpData);
			}
		}
	}
	
	/**
	 * 
	 * @param kind 表类别：1/2/3/4/5_A/5_B...
	 * @param num 此类型表个数
	 * @param colChange 需要改变的列名
	 * @param changeValue 此列的值改变为changeValue
	 * @param colWhere 执行where条件判断的列
	 * @param changeWhere where条件
	 * @throws DaoException
	 */
	public void updateData(String kind, int num, String colChange, String changeValue, String colWhere, String changeWhere) throws DaoException {
		String tablePrefix = new String();
		if(kind.startsWith("5")){
			tablePrefix =  "TEST_WITH_KEY_TS_";
		}else{
			int kindInt = Integer.parseInt(kind);
			switch (kindInt) {
			case 1:
				tablePrefix = "TEST_WITHOUT_KEY_TS_";
				break;
			case 2:
				tablePrefix = "TEST_WITH_KEY_";
				break;
			case 3:
				tablePrefix = "TEST_WITH_TS_";
				break;
			case 4:
				tablePrefix = "TEST_WITH_KEY_TS_";
				break;
			default:
				break;
			}
		}
		for (int i = 0; i < num; i++) {
			String code = kind + "_" + String.format("%02d", i + 1 );
			String tableName = tablePrefix + code;
			creatTableDao.updateData(tableName, colChange, changeValue, colWhere, changeWhere);
		}
	}
	
	/**
	 * 
	 * @param kind 表类别：1/2/3/4/5_A/5_B...
	 * @param num 此类型表个数
	 * @param colChange 需要改变的列名
	 * @param colWhere 执行where条件判断的列
	 * @param changeWhere where条件
	 * @throws DaoException
	 */
	public void deleteData(String kind, int num, String colWhere, String changeWhere) throws DaoException {
		String tablePrefix = new String();
		if(kind.startsWith("5")){
			tablePrefix =  "TEST_WITH_KEY_TS_";
		}else{
			int kindInt = Integer.parseInt(kind);
			switch (kindInt) {
			case 1:
				tablePrefix = "TEST_WITHOUT_KEY_TS_";
				break;
			case 2:
				tablePrefix = "TEST_WITH_KEY_";
				break;
			case 3:
				tablePrefix = "TEST_WITH_TS_";
				break;
			case 4:
				tablePrefix = "TEST_WITH_KEY_TS_";
				break;
			default:
				break;
			}
		}
		for (int i = 0; i < num; i++) {
			String code = kind + "_" + String.format("%02d", i + 1 );
			String tableName = tablePrefix + code;
			creatTableDao.deleteData(tableName, colWhere, changeWhere);
		}
	}

	public static final String randomString(int length) {
        if (length < 1) {
            return null;
        }
        if (randGen == null) {
               randGen = new Random();
               numbersAndLetters = ("0123456789abcdefghjklmnopqrstuvwxyz" +
                  "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ").toCharArray();
//               numbersAndLetters = ("0123456789abcdefghijklmnopqrstuvwxyz" +
//            		   "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ").toCharArray();
                 //numbersAndLetters = ("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ").toCharArray();
                }
        char [] randBuffer = new char[length];
        for (int i=0; i<randBuffer.length; i++) {
            randBuffer[i] = numbersAndLetters[randGen.nextInt(70)];
//            randBuffer[i] = numbersAndLetters[randGen.nextInt(71)];
         //randBuffer[i] = numbersAndLetters[randGen.nextInt(35)];
        }
        return new String(randBuffer);
	}
	
	public int randomInt(int min, int max) {
		return (int)(min+Math.random()*(max-min+1));
	}
	
	public int randomFloat(int min, int max) {
		return (int)(min+Math.random()*(max-min+1));
	}
	public void insertData(String tableName) throws DaoException {
			InsertData tmpData = new InsertData();
			tmpData.setCol_1(randomString(1));
			tmpData.setCol_2("e8a2f826a1db4113a894ea2cff159c33");
			tmpData.setCol_3(UUIDGenerator.getUUID());
			tmpData.setCol_4(randomFloat(1,9999));
			tmpData.setCol_5(randomInt(0,9));
			tmpData.setCol_6((float)(randomInt(0,99999999)/100));
			tmpData.setCol_7(UUIDGenerator.getUUID());
			tmpData.setCol_8(UUIDGenerator.getUUID());
			tmpData.setCol_9(UUIDGenerator.getUUID());
			creatTableDao.insertDataNoFK(tableName, tmpData);
		}
	
	public boolean isPKError(SQLException e) {  
		  
		  if (e == null) return false;  
		  
		  int nErrCode = e.getErrorCode();  
		  
		  if (nErrCode == 2627)  
		  
		    return true;  
		  
		  return false;  
		  
		}  
}
